{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5a535a2e-f428-42c4-9118-38c86c892c42",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "RangeIndex(start=0, stop=4, step=1)\n",
      "[10 20 30 40]\n",
      "int64\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "\n",
    "# Series：一维标签化数组\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 从列表创建（自动生成整数索引）\n",
    "s1 = pd.Series([10, 20, 30, 40])\n",
    "print(s1.index)    # RangeIndex(start=0, stop=4, step=1)\n",
    "print(s1.values)   # [10 20 30 40]\n",
    "print(s1.dtype)    # int64\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "863a4d63-5c36-4c7b-9aa2-2de17ef38888",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['a', 'b', 'c'], dtype='object')\n",
      "[5. 6. 7.]\n",
      "float32\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# 指定索引和数据类型\n",
    "s2 = pd.Series(data=[5, 6, 7], index=['a', 'b', 'c'], dtype=np.float32)\n",
    "print(s2.index)    # Index(['a', 'b', 'c'], dtype='object')\n",
    "print(s2.values)   # [5. 6. 7.]\n",
    "print(s2.dtype)    # float32\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "623b2f05-4259-4f88-95d3-4e7e1c85d59e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['北京', '上海', '广州'], dtype='object')\n",
      "[2154 2428 1867]\n",
      "int64\n",
      "上海    2428\n",
      "广州    1867\n",
      "Name: 人口(万), dtype: int64 ---ss---\n",
      "北京    2154\n",
      "上海    2428\n",
      "Name: 人口(万), dtype: int64 -------\n",
      "上海    2628.0\n",
      "北京    2254.0\n",
      "广州       NaN\n",
      "深圳       NaN\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# 从字典创建（键作为索引）\n",
    "s3 = pd.Series({'北京': 2154, '上海': 2428, '广州': 1867})\n",
    "print(s3.index)    # Index(['北京', '上海', '广州'], dtype='object')\n",
    "print(s3.values)   # [2154 2428 1867]\n",
    "print(s3.dtype)    # int64\n",
    "s3.name = \"人口(万)\"  # 设置Series名称\n",
    "\n",
    "# 1. 高级索引\n",
    "print(s3[['上海', '广州']],\"---ss---\")  # 标签索引\n",
    "print(s3[s3 > 2000], \"-------\")       # 布尔索引\n",
    "\n",
    "# 2. 矢量化运算（自动对齐索引）\n",
    "s4 = pd.Series({'北京': 100, '上海': 200, '深圳': 300})\n",
    "print(s3 + s4)  # 深圳→NaN（索引对齐）\n",
    "\n",
    "# 3. 函数应用\n",
    "log_pop = s3.apply(np.log)  # 应用对数函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "207651db-ab87-4d80-97d6-33f743bd124f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5d187aca-c9c5-405e-beca-4aad3be9a400",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 从字典创建（键→列名）\n",
    "data = {\n",
    "    '城市': ['北京', '上海', '广州'],\n",
    "    'GDP(亿)': [40269, 43214, 28232],\n",
    "    '人口(万)': [2154, 2428, 1867]\n",
    "}\n",
    "df = pd.DataFrame(data, index=['A', 'B', 'C'])\n",
    "\n",
    "# 从嵌套列表创建（指定列名）\n",
    "df2 = pd.DataFrame(\n",
    "    [[40269, 2154], [43214, 2428], [28232, 1867]],\n",
    "    columns=['GDP(亿)', '人口(万)'],\n",
    "    index=['北京', '上海', '广州']\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a4c474f8-b476-44e5-930a-eb3efb59dbb9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['城市', 'GDP(亿)', '人口(万)'], dtype='object')\n",
      "Index(['A', 'B', 'C'], dtype='object')\n",
      "(3, 3)\n",
      "城市        object\n",
      "GDP(亿)     int64\n",
      "人口(万)      int64\n",
      "dtype: object\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 3 entries, A to C\n",
      "Data columns (total 3 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   城市      3 non-null      object\n",
      " 1   GDP(亿)  3 non-null      int64 \n",
      " 2   人口(万)   3 non-null      int64 \n",
      "dtypes: int64(2), object(1)\n",
      "memory usage: 96.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "print(df.columns)  # Index(['城市', 'GDP(亿)', '人口(万)'], dtype='object')\n",
    "print(df.index)    # Index(['A', 'B', 'C'], dtype='object')\n",
    "print(df.shape)    # (3, 3)\n",
    "print(df.dtypes)   # 各列数据类型\n",
    "df.info()          # 内存布局和数据类型摘要"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95c5a3e0-5588-4315-86f8-b886b352e309",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f5f96b9e-b77c-416f-9a93-ce5e03d74280",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "城市                   北京\n",
      "GDP(亿)            40269\n",
      "人均GDP     186949.860724\n",
      "Name: A, dtype: object\n",
      "   城市          人均GDP\n",
      "A  北京  186949.860724\n",
      "D  深圳       8.200000\n",
      "   GDP(亿)          人均GDP\n",
      "A   40269  186949.860724\n",
      "B   43214  177981.878089\n"
     ]
    }
   ],
   "source": [
    "# 1. 列操作\n",
    "df['人均GDP'] = df['GDP(亿)'] / df['人口(万)'] * 10000  # 新增列\n",
    "df.drop(columns=['人口(万)'], inplace=True)          # 删除列\n",
    "\n",
    "# 2. 行操作（loc基于标签，iloc基于位置）\n",
    "print(df.loc['A'])                 # 获取索引'A'的行\n",
    "df.loc['D'] = ['深圳', 30665, 8.2]  # 新增行\n",
    "df.drop(index='C', inplace=True)    # 删除行\n",
    "\n",
    "# 3. 高级索引\n",
    "print(df.loc[['A', 'D'], ['城市', '人均GDP']])  # 行列标签组合\n",
    "print(df.iloc[0:2, 1:3])                      # 位置切片\n",
    "\n",
    "# 4. 条件筛选\n",
    "high_gdp = df[df['GDP(亿)'] > 40000]  # GDP超4万亿的城市"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "833c8bb3-8f79-48b6-83ca-5096f1b6f2a9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "97341577-91a6-4fc9-a2e8-b00cf58da1d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Series运算自动对齐索引\n",
    "s_pop = df.set_index('城市')['人口(万)']\n",
    "s_gdp = pd.Series({'北京':40269, '上海':43214, '重庆':27894})\n",
    "print(s_gdp / s_pop)  # 重庆不会计算（索引不匹配）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af28aeab-38bc-438d-ba20-05646c304f02",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c57f948-dab8-4d2b-81e8-b980d109fed3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f6d80b0b-4b1a-4017-a1a9-dbe9c95a3417",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "GDP    40269\n",
      "Name: (北京, 2020), dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 创建分层索引DataFrame\n",
    "arrays = [['北京','北京','上海','上海'], [2019,2020,2019,2020]]\n",
    "multi_index = pd.MultiIndex.from_arrays(arrays, names=('城市','年份'))\n",
    "df_multi = pd.DataFrame({'GDP':[35371,40269,38155,43214]}, index=multi_index)\n",
    "\n",
    "# 查询特定层级\n",
    "print(df_multi.loc[('北京',2020)])  # GDP:40269"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a200db1b-134f-4d8b-b5b8-c5096765f7ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 优化内存占用\n",
    "df['GDP(亿)'] = pd.to_numeric(df['GDP(亿)'], downcast='integer')\n",
    "df['人均GDP'] = pd.to_numeric(df['人均GDP'], downcast='float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3079f0e3-5a09-494f-be72-dc79061a84f6",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_179661/1095123270.py:1: FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.\n",
      "  df.loc['E'] = [None, None, None]  # 新增空行\n",
      "/tmp/ipykernel_179661/1095123270.py:2: FutureWarning: Downcasting object dtype arrays on .fillna, .ffill, .bfill is deprecated and will change in a future version. Call result.infer_objects(copy=False) instead. To opt-in to the future behavior, set `pd.set_option('future.no_silent_downcasting', True)`\n",
      "  df.fillna(0, inplace=True)        # 填充缺失值\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>城市</th>\n",
       "      <th>GDP(亿)</th>\n",
       "      <th>人均GDP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>北京</td>\n",
       "      <td>40269</td>\n",
       "      <td>186949.860724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>上海</td>\n",
       "      <td>43214</td>\n",
       "      <td>177981.878089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>深圳</td>\n",
       "      <td>30665</td>\n",
       "      <td>8.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   城市  GDP(亿)          人均GDP\n",
       "A  北京   40269  186949.860724\n",
       "B  上海   43214  177981.878089\n",
       "D  深圳   30665       8.200000\n",
       "E   0       0       0.000000"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc['E'] = [None, None, None]  # 新增空行\n",
    "df.fillna(0, inplace=True)        # 填充缺失值\n",
    "df.dropna(axis=0, how='any')       # 删除含空值的行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5196e2b-1ccb-4740-bbc1-337a162b1a48",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "829ba156-e920-402d-94c2-324a2a4bad99",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "UsageError: Line magic function `%%timeit` not found.\n"
     ]
    }
   ],
   "source": [
    "import timeit\n",
    "# 低效循环\n",
    "%%timeit\n",
    "new_col = []\n",
    "for i in range(len(df)):\n",
    "    new_col.append(df.iloc[i]['GDP(亿)'] * 1.1)\n",
    "\n",
    "# 高效矢量化\n",
    "%%timeit\n",
    "df['GDP_adj'] = df['GDP(亿)'] * 1.1  # 快10-100倍"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0e6bb58-287e-493d-89e7-c32f0b6be335",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "15636486-de4d-42c7-b4aa-57c6def63d59",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "循环方法10次耗时: 2.53755 秒\n",
      "矢量化方法10次耗时: 0.00172 秒\n",
      "矢量化比循环快 1472.0 倍\n"
     ]
    }
   ],
   "source": [
    "import timeit\n",
    "\n",
    "# 创建一个较大的DataFrame用于演示\n",
    "np.random.seed(0)\n",
    "df_large = pd.DataFrame(np.random.rand(10000, 5), columns=list('ABCDE'))\n",
    "\n",
    "# 低效循环\n",
    "def loop_method():\n",
    "    new_col = []\n",
    "    for i in range(len(df_large)):\n",
    "        new_col.append(df_large.iloc[i]['A'] * 1.1)\n",
    "    df_large['A_loop'] = new_col\n",
    "\n",
    "# 高效矢量化\n",
    "def vectorized_method():\n",
    "    df_large['A_vector'] = df_large['A'] * 1.1\n",
    "\n",
    "# 计时\n",
    "loop_time = timeit.timeit(loop_method, number=10)\n",
    "vector_time = timeit.timeit(vectorized_method, number=10)\n",
    "\n",
    "print(f\"循环方法10次耗时: {loop_time:.5f} 秒\")\n",
    "print(f\"矢量化方法10次耗时: {vector_time:.5f} 秒\")\n",
    "print(f\"矢量化比循环快 {loop_time/vector_time:.1f} 倍\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f3c2b7c-7430-46db-81b9-17bda8756bd6",
   "metadata": {},
   "source": [
    "# 关键结论：\n",
    "\n",
    "# 矢量化操作利用 Pandas 底层优化的 C/Fortran 代码，比 Python 循环快 10-100 倍\n",
    "\n",
    "# 数据量越大，性能差距越显著\n",
    "\n",
    "# 避免在 Pandas 中使用 for 循环处理数据，优先使用内置向量操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6776afa2-c328-4776-b009-b7695802487c",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48cd4d5f-020e-49ad-b5ca-b1454ffc1982",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ee4fe91-9e23-4cd6-acbd-11c91ee4969d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8172db59-7862-41de-8082-b2d069dba0c1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始数据：\n",
      "      A    B    C        D\n",
      "0  1.0    a  0.1  2023-01\n",
      "1  NaN    b  0.2     None\n",
      "2  3.0  NaN  0.3  2023-03\n",
      "3  4.0    d  NaN  2023-04\n",
      "4  NaN    e  0.5         \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 创建示例数据\n",
    "data = {'A': [1, np.nan, 3, 4, np.nan],\n",
    "        'B': ['a', 'b', np.nan, 'd', 'e'],\n",
    "        'C': [0.1, 0.2, 0.3, np.nan, 0.5],\n",
    "        'D': ['2023-01', None, '2023-03', '2023-04', '']}\n",
    "df = pd.DataFrame(data)\n",
    "print(\"原始数据：\\n\", df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3e018d4b-7c60-412c-9cdc-a7220e3c5285",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "缺失值检测：\n",
      "        A      B      C      D\n",
      "0  False  False  False  False\n",
      "1   True  False  False   True\n",
      "2  False   True  False  False\n",
      "3  False  False   True  False\n",
      "4   True  False  False  False\n",
      "\n",
      "列缺失统计：\n",
      " A    2\n",
      "B    1\n",
      "C    1\n",
      "D    1\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 检测缺失值\n",
    "print(\"缺失值检测：\\n\", df.isna())  # 等价于 df.isnull()\n",
    "\n",
    "\"\"\"\n",
    "输出：\n",
    "       A      B      C      D\n",
    "0  False  False  False  False\n",
    "1   True  False  False   True\n",
    "2  False   True  False  False\n",
    "3  False  False   True  False\n",
    "4   True  False  False   True\n",
    "\n",
    "注释：\n",
    "- isna() 和 isnull() 完全等价\n",
    "- 空字符串/None会被识别为缺失值\n",
    "- 注意：Python空字符串''在pandas中不被自动识别为NaN，需手动转换\n",
    "\"\"\"\n",
    "\n",
    "# 统计每列缺失值数量\n",
    "print(\"\\n列缺失统计：\\n\", df.isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "727beb4f-fd2f-4445-b54f-3aecc8ccca9a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "删除缺失值后：\n",
      "      A    B    C        D\n",
      "0  1.0    a  0.1  2023-01\n",
      "2  3.0  NaN  0.3  2023-03\n"
     ]
    }
   ],
   "source": [
    "# 删除缺失值\n",
    "df_drop = df.dropna(axis=0, how='any', subset=['A', 'C'])\n",
    "print(\"\\n删除缺失值后：\\n\", df_drop)\n",
    "\n",
    "# \"\"\"\n",
    "# 输出：\n",
    "#      A    B    C        D\n",
    "# 0  1.0    a  0.1  2023-01\n",
    "\n",
    "# 参数解析：\n",
    "# - axis=0: 删除行 (默认)\n",
    "# - how='any': 存在缺失即删除 (默认)；'all'表示全为缺失才删除\n",
    "# - subset: 指定判断列（此处只检查A、C列）\n",
    "# - inplace: 是否替换原数据（谨慎使用）\n",
    "# \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "07e6ff7e-4560-4a4e-9cc6-8cb7c9682561",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "填充后：\n",
      "           A        B    C        D\n",
      "0  1.000000        a  0.1  2023-01\n",
      "1  2.666667        b  0.2  missing\n",
      "2  3.000000  unknown  0.3  2023-03\n",
      "3  4.000000        d  0.3  2023-04\n",
      "4  2.666667        e  0.5         \n",
      "\n",
      "插值处理：\n",
      "      C  C_interp\n",
      "0  0.1       0.1\n",
      "1  0.2       0.2\n",
      "2  0.3       0.3\n",
      "3  NaN       0.4\n",
      "4  0.5       0.5\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "# 填充缺失值\n",
    "df_fill = df.fillna({'A': df['A'].mean(),  # 数值列用均值填充\n",
    "                     'B': 'unknown',       # 字符列用固定值\n",
    "                     'C': df['C'].ffill(), # 向前填充\n",
    "                     'D': 'missing'})      # 自定义填充\n",
    "print(\"\\n填充后：\\n\", df_fill)\n",
    "\n",
    "# 插值法（时间序列常用）\n",
    "df['C_interp'] = df['C'].interpolate(method='linear')  # 线性插值\n",
    "print(\"\\n插值处理：\\n\", df[['C', 'C_interp']])\n",
    "\n",
    "# \"\"\"\n",
    "# 高级填充技巧：\n",
    "# 1. 分组填充：按类别分组后填充\n",
    "#    df.groupby('category')['value'].transform(lambda x: x.fillna(x.mean()))\n",
    "   \n",
    "# 2. 多重插值：使用IterativeImputer（需sklearn）\n",
    "#    from sklearn.experimental import enable_iterative_imputer\n",
    "#    from sklearn.impute import IterativeImputer\n",
    "# \"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b162d4dd-447e-4847-a9fe-670464bc6dff",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d6b81580-75c7-498f-8cb1-822de043eea0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "原始数据：\n",
      "   Product  Price\n",
      "0       A    100\n",
      "1       B    200\n",
      "2       A    100\n",
      "3       C    300\n",
      "4       B    200\n",
      "5       B    200\n",
      "\n",
      "重复检测：\n",
      " 0    False\n",
      "1    False\n",
      "2     True\n",
      "3    False\n",
      "4     True\n",
      "5     True\n",
      "dtype: bool\n",
      "\n",
      "去重结果：\n",
      "   Product  Price\n",
      "2       A    100\n",
      "3       C    300\n",
      "5       B    200\n"
     ]
    }
   ],
   "source": [
    "# 创建含重复数据\n",
    "dup_data = {'Product': ['A', 'B', 'A', 'C', 'B', 'B'],\n",
    "            'Price': [100, 200, 100, 300, 200, 200]}\n",
    "df_dup = pd.DataFrame(dup_data)\n",
    "print(\"\\n原始数据：\\n\", df_dup)\n",
    "\n",
    "# 检测重复值\n",
    "print(\"\\n重复检测：\\n\", df_dup.duplicated(subset=['Product', 'Price'], keep='first'))\n",
    "\n",
    "\"\"\"\n",
    "输出：\n",
    "0    False\n",
    "1    False\n",
    "2     True  # 标记为重复（与第0行相同）\n",
    "3    False\n",
    "4     True  # 与第1行相同\n",
    "5     True  # 与第1行相同\n",
    "\n",
    "参数：\n",
    "- keep='first': 保留首次出现（默认）\n",
    "- keep='last': 保留最后一次出现\n",
    "- keep=False: 标记所有重复项\n",
    "\"\"\"\n",
    "\n",
    "# 删除重复值\n",
    "df_unique = df_dup.drop_duplicates(\n",
    "    subset=['Product'],  # 按产品去重\n",
    "    keep='last',         # 保留最后一条记录\n",
    "    inplace=False\n",
    ")\n",
    "print(\"\\n去重结果：\\n\", df_unique)\n",
    "\n",
    "# \"\"\"\n",
    "# 输出：\n",
    "#   Product  Price\n",
    "# 2       A    100  # 保留A的最后一条\n",
    "# 4       B    200  # 保留B的最后一条（第5行也是B，但被删除）\n",
    "# 3       C    300\n",
    "\n",
    "# 应用场景：\n",
    "# - 用户行为数据：保留最近记录\n",
    "# - 交易数据：保留金额最大的记录（需配合sort_values）\n",
    "# \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "566cf5af-7889-43a4-85af-704d53cfa8b5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "转换错误: could not convert string to float: '三百'\n",
      "\n",
      "安全转换结果：\n",
      " 0    100.0\n",
      "1    200.0\n",
      "2      NaN\n",
      "3    400.5\n",
      "Name: Price_fixed, dtype: float64\n",
      "\n",
      "日期转换：\n",
      " 0   2023-01-01\n",
      "1   2023-02-01\n",
      "2          NaT\n",
      "3          NaT\n",
      "Name: Date_parsed, dtype: datetime64[ns]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "\n",
    "# 类型转换示例\n",
    "dtype_data = {'Price': ['100', '200', '三百', '400.5'],  # 含非法字符\n",
    "              'Date': ['2023-01-01', '2023/02/01', '2023年3月1日', None]}\n",
    "df_dtype = pd.DataFrame(dtype_data)\n",
    "\n",
    "# 常规转换（遇到错误会抛出异常）\n",
    "try:\n",
    "    df_dtype['Price'] = df_dtype['Price'].astype(float)\n",
    "except Exception as e:\n",
    "    print(f\"\\n转换错误: {e}\")\n",
    "\n",
    "# 安全转换（pd.to_numeric）\n",
    "df_dtype['Price_fixed'] = pd.to_numeric(\n",
    "    df_dtype['Price'], \n",
    "    errors='coerce',  # 将错误转为NaN\n",
    "    downcast='integer'  # 向下转换节省内存\n",
    ")\n",
    "print(\"\\n安全转换结果：\\n\", df_dtype['Price_fixed'])\n",
    "\n",
    "# 日期转换（处理多格式日期）\n",
    "df_dtype['Date_parsed'] = pd.to_datetime(\n",
    "    df_dtype['Date'],\n",
    "    errors='coerce',\n",
    "    format='mixed'  # Pandas 2.0+支持混合格式\n",
    ")\n",
    "print(\"\\n日期转换：\\n\", df_dtype['Date_parsed'])\n",
    "\n",
    "# \"\"\"\n",
    "# 输出：\n",
    "# 0   100.0\n",
    "# 1   200.0\n",
    "# 2     NaN  # 非法字符被替换\n",
    "# 3   400.5\n",
    "# Name: Price_fixed, dtype: float64\n",
    "\n",
    "# 0   2023-01-01\n",
    "# 1   2023-02-01\n",
    "# 2          NaT  # 解析失败\n",
    "# 3          NaT\n",
    "# Name: Date_parsed, dtype: datetime64[ns]\n",
    "\n",
    "# 进阶技巧：\n",
    "# 1. 自定义转换函数：\n",
    "#    df['col'].apply(lambda x: custom_parser(x))\n",
    "   \n",
    "# 2. 处理日期组件：\n",
    "#    df['Year'] = df['Date_parsed'].dt.year\n",
    "# \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa23d061-e8a7-4afa-91cf-467d7db40a01",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "74675a57-1efd-448f-a27f-4be7883f52d6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "字符串处理结果：\n",
      "     ID         Name           Email   Name_upper  is_valid_email    Domain  \\\n",
      "0  A001     john DOE   john@test.com     JOHN DOE            True  test.com   \n",
      "1  B002  Alice Smith  alice@demo.net  ALICE SMITH            True  demo.net   \n",
      "2  C003    BOB JONES   invalid_email    BOB JONES           False       NaN   \n",
      "\n",
      "   First   Last  \n",
      "0   john    DOE  \n",
      "1  Alice  Smith  \n",
      "2    BOB  JONES  \n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_179661/1357005743.py:11: UserWarning: This pattern is interpreted as a regular expression, and has match groups. To actually get the groups, use str.extract.\n",
      "  df_text['is_valid_email'] = df_text['Email'].str.contains(r'@[a-z]+\\.(com|net)$', regex=True)\n"
     ]
    }
   ],
   "source": [
    "# 字符串处理示例\n",
    "text_data = {'ID': ['A001', 'B002', 'C003'],\n",
    "             'Name': ['john DOE', 'Alice Smith', 'BOB JONES'],\n",
    "             'Email': ['john@test.com', 'alice@demo.net', 'invalid_email']}\n",
    "df_text = pd.DataFrame(text_data)\n",
    "\n",
    "# 大小写转换\n",
    "df_text['Name_upper'] = df_text['Name'].str.upper()\n",
    "\n",
    "# 包含检测\n",
    "df_text['is_valid_email'] = df_text['Email'].str.contains(r'@[a-z]+\\.(com|net)$', regex=True)\n",
    "\n",
    "# 正则提取\n",
    "df_text['Domain'] = df_text['Email'].str.extract(r'@([a-z]+\\.\\w+)', expand=False)\n",
    "\n",
    "# 分列操作\n",
    "df_text[['First', 'Last']] = df_text['Name'].str.split(n=1, expand=True)\n",
    "\n",
    "print(\"\\n字符串处理结果：\")\n",
    "print(df_text)\n",
    "\n",
    "# \"\"\"\n",
    "# 输出：\n",
    "#      ID        Name            Email  ...  is_valid_email     Domain First   Last\n",
    "# 0  A001    john DOE   john@test.com  ...            True  test.com  john    DOE\n",
    "# 1  B002  Alice Smith  alice@demo.net  ...            True  demo.net Alice  Smith\n",
    "# 2  C003    BOB JONES  invalid_email  ...           False        NaN   BOB  JONES\n",
    "\n",
    "# 常用方法：\n",
    "# 1. 过滤：str.startswith(), str.endswith()\n",
    "# 2. 替换：str.replace(r'\\d+', 'X')  # 替换数字\n",
    "# 3. 格式化：str.strip(), str.pad(width=10)\n",
    "# 4. 分词：str.split(), str.get_dummies()\n",
    "\n",
    "# 正则表达式进阶：\n",
    "# - 提取多组：str.extract(r'(\\d+)([A-Z])')\n",
    "# - 条件替换：str.replace(r'(?P<name>\\w+)', lambda x: x.group('name').upper())\n",
    "# \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e88e7c0-13f3-497c-90e2-2af299eaeae5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b6ce64e-af0b-454b-97e7-170c1822004b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "16c84862-50e5-42d4-b968-3f56bbb77cec",
   "metadata": {},
   "source": [
    "# 综合应用场景"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "031ef552-f3c5-4699-b97e-e204917f5f1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1. 处理缺失\n",
    "df.fillna({'price': df['price'].median(), \n",
    "           'category': 'Other'}, inplace=True)\n",
    "\n",
    "# 2. 删除重复\n",
    "df = df.sort_values('order_time', ascending=False)\n",
    "df = df.drop_duplicates(subset='order_id', keep='first')\n",
    "\n",
    "# 3. 类型转换\n",
    "df['price'] = pd.to_numeric(df['price'], errors='coerce')\n",
    "df['order_time'] = pd.to_datetime(df['order_time'])\n",
    "\n",
    "# 4. 字符串清洗\n",
    "df['product_name'] = df['product_name'].str.strip().str.title()\n",
    "df['valid_email'] = df['email'].str.match(r'^[\\w\\.-]+@[a-zA-Z]+\\.[a-z]{2,3}$')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a4693a57-033f-496e-9b01-e75cca8167c6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4de4b39b-57d3-47d6-add1-f7b339136e51",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "8d64f36b-eb92-4d4b-a929-6fa7e43e5776",
   "metadata": {},
   "source": [
    "# 纵向合并 pd.concat([df1, df2])\n",
    "# 将多个 DataFrame 沿垂直方向堆叠（行拼接）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2cf436d8-ba95-4d55-b6ec-6657116c35b7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   ID     Name  Age\n",
      "0   1    Alice   25\n",
      "1   2      Bob   30\n",
      "2   3  Charlie   35\n",
      "3   4    David   28\n",
      "4   5      Eva   32\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df1 = pd.DataFrame({\n",
    "    'ID': [1, 2, 3],\n",
    "    'Name': ['Alice', 'Bob', 'Charlie'],\n",
    "    'Age': [25, 30, 35]\n",
    "})\n",
    "\n",
    "df2 = pd.DataFrame({\n",
    "    'ID': [4, 5],\n",
    "    'Name': ['David', 'Eva'],\n",
    "    'Age': [28, 32]\n",
    "})\n",
    "\n",
    "# 纵向合并（默认axis=0）\n",
    "result = pd.concat([df1, df2], ignore_index=True)  # ignore_index重置索引\n",
    "print(result)\n",
    "# \"\"\"\n",
    "#    ID     Name  Age\n",
    "# 0   1    Alice   25\n",
    "# 1   2      Bob   30\n",
    "# 2   3  Charlie   35\n",
    "# 3   4    David   28\n",
    "# 4   5      Eva   32\n",
    "# \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "847bc944-c39a-4890-aa01-9ccfef19d69c",
   "metadata": {},
   "source": [
    "# pd.merge()：基于键值连接（类似SQL JOIN）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "ed411d86-e30d-4ff4-9d11-df7588447ad5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   OrderID  CustomerID  Amount   Name\n",
      "0      101           1    99.5  Alice\n",
      "1      102           2    50.0    Bob\n",
      "   OrderID  CustomerID  Amount   Name\n",
      "0      101           1    99.5  Alice\n",
      "1      102           2    50.0    Bob\n",
      "2      103           3   120.0    NaN\n"
     ]
    }
   ],
   "source": [
    "\n",
    "orders = pd.DataFrame({\n",
    "    'OrderID': [101, 102, 103],\n",
    "    'CustomerID': [1, 2, 3],\n",
    "    'Amount': [99.5, 50.0, 120.0]\n",
    "})\n",
    "\n",
    "customers = pd.DataFrame({\n",
    "    'CustomerID': [1, 2, 4],\n",
    "    'Name': ['Alice', 'Bob', 'David']\n",
    "})\n",
    "\n",
    "# 内连接（默认）\n",
    "merged = pd.merge(orders, customers, on='CustomerID')\n",
    "print(merged)\n",
    "# \"\"\"\n",
    "#    OrderID  CustomerID  Amount   Name\n",
    "# 0      101           1    99.5  Alice\n",
    "# 1      102           2    50.0    Bob\n",
    "# \"\"\"\n",
    "\n",
    "# 左连接\n",
    "left_merged = pd.merge(orders, customers, on='CustomerID', how='left')\n",
    "print(left_merged)\n",
    "# \"\"\"\n",
    "#    OrderID  CustomerID  Amount   Name\n",
    "# 0      101           1    99.5  Alice\n",
    "# 1      102           2    50.0    Bob\n",
    "# 2      103           3   120.0    NaN\n",
    "# \"\"\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9776edf-7ebc-4642-90af-1a757ec701a0",
   "metadata": {},
   "source": [
    "# df.join()：基于索引连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "65b263eb-7544-4577-9821-8e08e51e211d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B\n",
      "x  1  3\n",
      "y  2  4\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "df1 = pd.DataFrame({'A': [1, 2]}, index=['x', 'y'])\n",
    "df2 = pd.DataFrame({'B': [3, 4]}, index=['x', 'y'])\n",
    "\n",
    "# 横向连接索引\n",
    "joined = df1.join(df2)\n",
    "print(joined)\n",
    "# \"\"\"\n",
    "#    A  B\n",
    "# x  1  3\n",
    "# y  2  4\n",
    "# \"\"\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c848ef2e-9d02-429e-b724-612f3b62fc84",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "\n",
    "# 透视表 pd.pivot_table()\n",
    "# 数据汇总工具，支持多维度聚合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "1052733c-44fd-465d-89f1-f4663ff410bf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Product    A    B\n",
      "Date             \n",
      "2023-01  100  150\n",
      "2023-02  200  250\n"
     ]
    }
   ],
   "source": [
    "sales = pd.DataFrame({\n",
    "    'Date': ['2023-01', '2023-01', '2023-02', '2023-02'],\n",
    "    'Product': ['A', 'B', 'A', 'B'],\n",
    "    'Revenue': [100, 150, 200, 250],\n",
    "    'Quantity': [10, 15, 20, 25]\n",
    "})\n",
    "\n",
    "# 创建透视表：按日期和产品聚合\n",
    "pivot = pd.pivot_table(\n",
    "    sales,\n",
    "    index='Date',           # 行索引\n",
    "    columns='Product',      # 列索引\n",
    "    values='Revenue',       # 聚合值\n",
    "    aggfunc='sum',          # 聚合函数\n",
    "    fill_value=0            # 缺失值填充\n",
    ")\n",
    "print(pivot)\n",
    "# \"\"\"\n",
    "# Product     A    B\n",
    "# Date              \n",
    "# 2023-01   100  150\n",
    "# 2023-02   200  250\n",
    "# \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51028deb-c15c-4416-ae5e-cf71b3cfbc11",
   "metadata": {},
   "source": [
    "# 融化 pd.melt()\n",
    "# 宽表转长表（列转行）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f0db85e1-cfab-4aff-a934-d53ad97bb770",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       City  Year   GDP\n",
      "0   Beijing  2022  2150\n",
      "1  Shanghai  2022  2480\n",
      "2   Beijing  2023  2250\n",
      "3  Shanghai  2023  2600\n"
     ]
    }
   ],
   "source": [
    "wide_df = pd.DataFrame({\n",
    "    'City': ['Beijing', 'Shanghai'],\n",
    "    '2022': [2150, 2480],\n",
    "    '2023': [2250, 2600]\n",
    "})\n",
    "\n",
    "melted = pd.melt(\n",
    "    wide_df,\n",
    "    id_vars='City',         # 保留列\n",
    "    value_vars=['2022', '2023'],  # 转换列\n",
    "    var_name='Year',        # 新列名（原列名）\n",
    "    value_name='GDP'        # 新列名（原数值）\n",
    ")\n",
    "print(melted)\n",
    "# \"\"\"\n",
    "#        City  Year   GDP\n",
    "# 0   Beijing  2022  2150\n",
    "# 1  Shanghai  2022  2480\n",
    "# 2   Beijing  2023  2250\n",
    "# 3  Shanghai  2023  2600\n",
    "# \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34bf763c-a5c4-491d-b8dc-94778f2f243d",
   "metadata": {},
   "source": [
    "# 转置 df.T\n",
    "# 行列互换（矩阵转置）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "9ad585ec-7bc2-4da1-b4c4-b186f80523ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Metrics   Q1   Q2\n",
      "0  Revenue  100  120\n",
      "1   Profit   20   25\n",
      "Metrics  Revenue  Profit\n",
      "Q1           100      20\n",
      "Q2           120      25\n"
     ]
    }
   ],
   "source": [
    "data = pd.DataFrame({\n",
    "    'Metrics': ['Revenue', 'Profit'],\n",
    "    'Q1': [100, 20],\n",
    "    'Q2': [120, 25]\n",
    "})\n",
    "print(data)\n",
    "transposed = data.set_index('Metrics').T  # 需先设置索引\n",
    "print(transposed)\n",
    "# \"\"\"\n",
    "# Metrics  Revenue  Profit\n",
    "# Q1           100      20\n",
    "# Q2           120      25\n",
    "# \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ea8907b-2069-42e7-9d08-548bd863bcda",
   "metadata": {},
   "source": [
    "# 分组 df.groupby()\n",
    "# 数据分组操作"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "b7e7de57-348c-453a-bdaa-85da129e1d6c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7b0da82b2710>\n",
      "{'HR': [0, 2], 'Tech': [1, 3]}\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame({\n",
    "    'Department': ['HR', 'Tech', 'HR', 'Tech'],\n",
    "    'Employee': ['Alice', 'Bob', 'Charlie', 'David'],\n",
    "    'Salary': [5000, 8000, 5500, 8500]\n",
    "})\n",
    "\n",
    "# 按部门分组\n",
    "grouped = df.groupby('Department')\n",
    "print(grouped.groups)  # 查看分组结构\n",
    "# \"\"\"\n",
    "# {'HR': [0, 2], 'Tech': [1, 3]}\n",
    "# \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0954a828-f6c9-48ad-8d0f-369959fd0de9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#  聚合 .agg()\n",
    "# 对分组应用聚合函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "8c94bb62-520c-4a31-a59e-ffcfe3698e0a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Department\n",
      "HR      5250.0\n",
      "Tech    8250.0\n",
      "Name: Salary, dtype: float64\n",
      "           Salary               Employee\n",
      "              sum    mean   max    count\n",
      "Department                              \n",
      "HR          10500  5250.0  5500        2\n",
      "Tech        16500  8250.0  8500        2\n"
     ]
    }
   ],
   "source": [
    "# 单列聚合\n",
    "print(grouped['Salary'].mean())\n",
    "# \"\"\"\n",
    "# Department\n",
    "# HR      5250\n",
    "# Tech    8250\n",
    "# \"\"\"\n",
    "\n",
    "# 多列多函数聚合\n",
    "agg_result = grouped.agg({\n",
    "    'Salary': ['sum', 'mean', 'max'],\n",
    "    'Employee': 'count'\n",
    "})\n",
    "print(agg_result)\n",
    "# \"\"\"\n",
    "#         Salary              Employee\n",
    "#           sum   mean   max    count\n",
    "# Department                         \n",
    "# HR      10500  5250  5500        2\n",
    "# Tech    16500  8250  8500        2\n",
    "# \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a28040b-dd03-4542-9b8f-7e996ad78ef1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "f6f1b6f5-ae42-402b-a6c2-9b0d75c9e38e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 示例数据\n",
    "data = {'Category': ['A', 'B', 'A', 'B', 'A'], \n",
    "        'Values': [10, 20, 30, 40, 50]}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 分组：按 Category 拆分\n",
    "grouped = df.groupby('Category')  # 返回 GroupBy 对象，不立即计算"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c53d81e-e169-4755-b0ac-43c230432365",
   "metadata": {},
   "source": [
    "# GroupBy 对象结构\n",
    "# grouped.groups：字典，键为分组名，值为对应行索引。\n",
    "# grouped.ngroups：分组数量（如 df.groupby(['A','B']).ngroups）。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59cc1b8b-14a8-4d97-94ee-8f601a21823a",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "# 单列聚合\n",
    "# 计算每组的平均值\n",
    "result = grouped['Values'].mean()\n",
    "print(result)\n",
    "\n",
    "\n",
    "\n",
    "# 同时计算总和、最大值、最小值\n",
    "agg_result = grouped.agg({\n",
    "    'Values': ['sum', 'max', 'min']  # 对 Values 列应用多个聚合\n",
    "})\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# 为聚合结果命名  自定义聚合命名\n",
    "result = grouped.agg(\n",
    "    avg_value=('Values', 'mean'),      # 平均值命名为 avg_value\n",
    "    range=('Values', lambda x: x.max() - x.min())  # 自定义极差计算\n",
    ")\n",
    "\n",
    "\n",
    "\n",
    "# 只保留组内平均值 >25 的分组\n",
    "filtered = grouped.filter(lambda group: group['Values'].mean() > 25)\n",
    "print(filtered)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# 组内标准化（Z-Score）\n",
    "df['Z-Score'] = grouped['Values'].transform(\n",
    "    lambda x: (x - x.mean()) / x.std()\n",
    ")\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# 多级分组与透视\n",
    "# 多列分组：按 Category 和子类分组\n",
    "multi_grouped = df.groupby(['Category', 'Subcategory'])\n",
    "# 多级聚合\n",
    "result = multi_grouped.agg({'Values': 'sum'}).unstack()  # 结果转换为透视表\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# 按时间周期分组（如按月）\n",
    "df['Date'] = pd.date_range(start='2024-01-01', periods=5, freq='D')\n",
    "month_group = df.groupby(pd.Grouper(key='Date', freq='M')).sum()\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# 每组取 Values 最大的前2行\n",
    "top2 = df.groupby('Category').apply(lambda x: x.nlargest(2, 'Values'))\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e4071cd-1b2c-4cdc-a03e-e8e4d4c6773d",
   "metadata": {},
   "source": [
    "# 避免 apply：尽量使用内置聚合（如 sum()）而非 apply(lambda x: x.sum())，速度可提升 10 倍以上。\n",
    "# 分步计算：复杂操作拆分为多个步骤（如先过滤再聚合）。\n",
    "# 使用 pd.Grouper：对时间数据分组时效率更高"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "b4e0e9c7-1d3d-478b-8931-3549ab943f34",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stock</th>\n",
       "      <th>Date</th>\n",
       "      <th>avg_price</th>\n",
       "      <th>volatility</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2024-01</td>\n",
       "      <td>150.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>2024-02</td>\n",
       "      <td>160.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GOOG</td>\n",
       "      <td>2024-01</td>\n",
       "      <td>2800.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GOOG</td>\n",
       "      <td>2024-02</td>\n",
       "      <td>2900.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Stock     Date  avg_price  volatility\n",
       "0  AAPL  2024-01      150.0           0\n",
       "1  AAPL  2024-02      160.0           0\n",
       "2  GOOG  2024-01     2800.0           0\n",
       "3  GOOG  2024-02     2900.0           0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "# 示例：股票数据按月分组统计\n",
    "data = {\n",
    "    'Stock': ['AAPL', 'GOOG', 'AAPL', 'GOOG'],\n",
    "    'Date': ['2024-01', '2024-01', '2024-02', '2024-02'],\n",
    "    'Price': [150, 2800, 160, 2900]\n",
    "}\n",
    "df_stock = pd.DataFrame(data)\n",
    "\n",
    "# 多列分组 + 定制聚合\n",
    "result = df_stock.groupby(['Stock', 'Date']).agg(\n",
    "    avg_price=('Price', 'mean'),\n",
    "    volatility=('Price', lambda x: x.max() - x.min())\n",
    ").reset_index()  # 取消索引化[9](@ref)\n",
    "\n",
    "\n",
    "result\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b26fa7f1-96ca-4a33-a68c-4150dc3d3347",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "93c47909-dcf8-47a4-8e29-1bd016101143",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "# 分箱与离散化\n",
    "\n",
    "# pd.cut()：等宽分箱（固定区间宽度）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "1d52987d-900a-457a-b8f9-c163021662f2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Young', 'Young', 'Young', 'Young', 'Middle', 'Middle', 'Senior', 'Senior', NaN]\n",
      "Categories (3, object): ['Young' < 'Middle' < 'Senior']\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "\n",
    "ages = [18, 22, 25, 27, 30, 35, 40, 45, 50]\n",
    "bins = [18, 30, 40, 50]  # 定义区间边界\n",
    "labels = ['Young', 'Middle', 'Senior']\n",
    "\n",
    "# 分箱操作\n",
    "age_categories = pd.cut(\n",
    "    ages,\n",
    "    bins=bins,\n",
    "    labels=labels,\n",
    "    right=False  # 左闭右开区间\n",
    ")\n",
    "print(age_categories)\n",
    "# \"\"\"\n",
    "# [Young, Young, Young, Young, Middle, ..., Senior, Senior, Senior]\n",
    "# Categories (3, object): ['Young' < 'Middle' < 'Senior']\n",
    "# \"\"\"\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8b59635-f899-43ae-8b36-47f8d4a980be",
   "metadata": {},
   "source": [
    "# 按消费金额划分客户等级。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "66125c2e-e381-4ae6-bdbb-4888b6779335",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_ID</th>\n",
       "      <th>Spent_Amount</th>\n",
       "      <th>消费等级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>150</td>\n",
       "      <td>低消费</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>350</td>\n",
       "      <td>中消费</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>500</td>\n",
       "      <td>中消费</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>700</td>\n",
       "      <td>高消费</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>950</td>\n",
       "      <td>高消费</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_ID  Spent_Amount 消费等级\n",
       "0        1           150  低消费\n",
       "1        2           350  中消费\n",
       "2        3           500  中消费\n",
       "3        4           700  高消费\n",
       "4        5           950  高消费"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "import pandas as pd\n",
    "data = {'User_ID': [1, 2, 3, 4, 5], 'Spent_Amount': [150, 350, 500, 700, 950]}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 定义分箱边界与标签\n",
    "bins = [0, 300, 600, float('inf')]\n",
    "labels = ['低消费', '中消费', '高消费']\n",
    "\n",
    "# 分箱并分配标签\n",
    "df['消费等级'] = pd.cut(df['Spent_Amount'], bins=bins, labels=labels, right=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "aa9f0cb1-40c9-4f54-a435-5f234bad71ee",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_179661/1836109965.py:1: FutureWarning: The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.\n",
      "  df.groupby('消费等级')['Spent_Amount'].mean()  # 计算各等级平均消费额\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "消费等级\n",
       "低消费    150.0\n",
       "中消费    425.0\n",
       "高消费    825.0\n",
       "Name: Spent_Amount, dtype: float64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('消费等级')['Spent_Amount'].mean()  # 计算各等级平均消费额"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e995195-3665-4b2f-b0ca-4fdf3d992398",
   "metadata": {},
   "source": [
    "# 时间序列周期性分析（能源领域）\n",
    "# 目标：按日耗电量划分时段，分析周分布规律。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "42db541e-18b4-4af4-b1ed-d5910297359a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_179661/2801924971.py:13: FutureWarning: The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.\n",
      "  weekly_dist = df.groupby(['耗电等级', '星期']).size().unstack()\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>星期</th>\n",
       "      <th>Friday</th>\n",
       "      <th>Monday</th>\n",
       "      <th>Saturday</th>\n",
       "      <th>Sunday</th>\n",
       "      <th>Thursday</th>\n",
       "      <th>Tuesday</th>\n",
       "      <th>Wednesday</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>耗电等级</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>低耗电</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>中耗电</th>\n",
       "      <td>44</td>\n",
       "      <td>42</td>\n",
       "      <td>44</td>\n",
       "      <td>43</td>\n",
       "      <td>44</td>\n",
       "      <td>42</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>高耗电</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "星期    Friday  Monday  Saturday  Sunday  Thursday  Tuesday  Wednesday\n",
       "耗电等级                                                                \n",
       "低耗电        3       8         9       9         5        8          6\n",
       "中耗电       44      42        44      43        44       42         43\n",
       "高耗电        5       2         0       0         3        2          3"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 生成模拟数据（365天）\n",
    "dates = pd.date_range(start='2022-01-01', end='2022-12-31')\n",
    "consumption = [200 + (i % 30)*10 + (i % 7)*5 for i in range(365)]\n",
    "df = pd.DataFrame({'Date': dates, 'Daily_Consumption': consumption})\n",
    "\n",
    "# 分箱并提取星期信息\n",
    "bins = [0, 250, 500, float('inf')]\n",
    "labels = ['低耗电', '中耗电', '高耗电']\n",
    "df['耗电等级'] = pd.cut(df['Daily_Consumption'], bins=bins, labels=labels, right=False)\n",
    "df['星期'] = df['Date'].dt.day_name()\n",
    "\n",
    "# 按等级和星期聚合\n",
    "weekly_dist = df.groupby(['耗电等级', '星期']).size().unstack()\n",
    "weekly_dist"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6f84fb2-eb96-41ff-b329-47ac48f7bc1e",
   "metadata": {},
   "source": [
    "# 特征工程（机器学习）\n",
    "# 目标：将年龄分段作为分类特征。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "7b027e19-3410-460d-abb0-d959093193ef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['青年', '中年', '少年', '青年', '老年']\n",
       "Categories (4, object): ['少年' < '青年' < '中年' < '老年']"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ages = [22, 45, 18, 30, 70]\n",
    "bins = [0, 18, 35, 60, 100]\n",
    "labels = ['少年', '青年', '中年', '老年']\n",
    "pd.cut(ages, bins=bins, labels=labels, include_lowest=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24e9d4ab-6748-449e-b0fc-b33c51d82ed3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c3598360-9615-4d52-92bc-03ccba77236d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
